from iotdb.Session import Session
import templates

# 总活跃度、评论、pr上传到mysql

com_json=templates.DATA_JSON()
db_mysql=templates.DB_MYSQL()

ip = "127.0.0.1"
port_ = "6667"
username_ = "root"
password_ = "root"
session = Session(ip, port_, username_, password_)
session.open()


class OPEN_MYSQL:
        # 执行sql
    def list_sql(self, combined_list):
        db_mysql.Connect()
        db_mysql.cursor = db_mysql.con.cursor()
        try:
            sql="INSERT IGNORE INTO items_gather (item,activity,issue_comments,change_requests) VALUES (%s,%s,%s,%s)"
            # 执行sql命令
            db_mysql.cursor.executemany(sql, combined_list)
            # print("执行成功")
        except Exception as e:
            print(e)
        finally:
            # 提交
            db_mysql.con.commit()
            db_mysql.cursor.close()
            db_mysql.con.close()

json_list=['activity','issue_comments','change_requests']
totals=[]
for i in range(100):
    table,goal=com_json.dir_json(i)
    total=[]
    total.append(table)
    for j in range(len(json_list)):
        result = session.execute_query_statement(f"select {json_list[j]} from root.opendigger.{table}")
        df = result.todf()
        sum = df[f'root.opendigger.{table}.{json_list[j]}'].sum()
        sum = round(sum, 2)
        total.append(sum)
    totals.append(total)


insert_sql=OPEN_MYSQL()
insert_sql.list_sql(totals)

session.close()